﻿"""
根据上传的不加系数数据与标准件进行比对，计算校准系数
校准第二步：将系数写入配置文件
"""
import os
import shutil
from datetime import datetime
import pyodbc
import pandas as pd
from sortingdataprocess import get_item_code, get_standpart_data, get_test_data, cal_coef
from setiniconfig import *
from getcobspec import *

# 创建SQL server sqlalchemy数据库连接

pd.set_option('display.max_columns', None)


def config_coef(form2_infos, machine_label, machine_name, cnxn, conn, engine, logger, CCT, CCT_String):
    """
    步骤二的参数说明
    :param form2_infos:步骤二的字典数据：工单号、批次号1、标准件型号、机台号、工号、临时文件路径、上传的文件名称、一步文件生成路径
    :param cnxn:权限高的数据库连接（用于更新、修改等）
    :param conn:权限低的数据库连接（用于查询）
    :param engine:权限高的数据库engine
    :param logger:整个flask的日志记录器
    :param CCT:双色温区分标志：low/high;低色温/正常 均使用low,高色温 使用high
    :param CCT_String:色温字符串，用于查询双色温对应标准件数据
    :return:
    """
    # 定义返回值，排除未定以返回值时程序报错
    flash_result = {}
    coefconf_path = None
    lotID2 = None
    upload_path = None
    # 创建游标
    cursor = cnxn.cursor()
    cursor.fast_executemany = True
    # 创建游标
    cursor1 = conn.cursor()
    cursor1.fast_executemany = True
    # 根据单号查询品号,步骤一验证通过，无需再次验证单号、品号
    item_code = get_item_code(form2_infos['MoID'])
    # 调用sortingdataprocess.py 中的 get_standpart_data 获取标准件数据
    std_data = get_standpart_data(item_code, CCT_String)
    # 调用sortingdataprocess.py 中的 get_test_data 读取测试数据
    test_data, error = get_test_data(form2_infos['temp_path'], machine_name, logger)

    # 情况1：标准件无数据
    if std_data.empty:
        logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--没有标准件数据")
        flash_result[1] = f"{machine_label}<br>该工单没有标准件有效数据,请交由实验室更新！"
    # 情况2：测试数据为空；或者读取异常
    elif test_data.empty:
        logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--上传为空的测试数据")
        flash_result[2] = f"{machine_label}<br>读取上传数据发生错误或者上传数据为空,截图反馈至实验室<br>问题描述:{error}"
    else:
        # 删除电流为NA和0，光通量为NA和0
        test_data = test_data[test_data['Current_mA'] != 0]
        test_data.dropna(subset=['Current_mA'], inplace=True)
        test_data = test_data[test_data['LuminousFlux_lm'] != 0]
        test_data.dropna(subset=['LuminousFlux_lm'], inplace=True)
        # 情况3：'5101-'开头工单，使用少于3条数据校准
        if form2_infos['MoID'].startswith('5101-') and len(test_data) < 3:
            logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--测试数据少于3条!")
            flash_result[3] = f"{machine_label}<br>请保证3个或3个以上标准灯正常点亮,有效数据条数:{len(test_data)}"
            return  flash_result, coefconf_path, lotID2, upload_path

        # 校准系数计算
        coef_data = cal_coef(std_data, test_data)
        # 情况4：校准系数为空,编号不对应
        if coef_data.empty:
            list1 = sorted(list(std_data['StandardPartNO']))
            logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--测试数据与标准件数据编号不对应!")
            flash_result[4] = f"{machine_label}<br>测试数据与标准件数据编号不对应,标准件编号:{list1}"
            return flash_result, coefconf_path, lotID2, upload_path
        # 情况5：电流值偏差过大
        elif not coef_data.empty and coef_data.loc['校准系数', 'dI'] > 0.05:
            logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--测试电流与标准件电流偏差超过5%!")
            flash_result[5] = "{machine_label}<br>测试电流与标准件电流偏差超过5%,交由实验室更新"
            return flash_result, coefconf_path, lotID2, upload_path

        # 查询工单+lotID组合下的LOTID2是否存在，存在+1，不存在初始化为1
        cursor.execute(
            f"SELECT MAX(LOTID2) FROM Gencoef_Info1 "
            f"WHERE MOID='{form2_infos['MoID']}' AND LotID='{form2_infos['LotID']}';")
        rows = cursor.fetchall()
        if rows[0][0] is None:
            lotID2 = 1
        else:
            lotID2 = rows[0][0] + 1

        # 添加LOTID2到列表
        form2_infos['LotID2'] = lotID2

        # 创建上传与下载目录并添加到列表
        uploadsDir = os.path.join('uploads', 'rawdata', str(datetime.now().year), str(datetime.now().month))
        if not os.path.exists(uploadsDir):
            os.makedirs(uploadsDir)
        downloadsDir = os.path.join('downloads', 'coefconf', str(datetime.now().year), str(datetime.now().month))
        if not os.path.exists(downloadsDir):
            os.makedirs(downloadsDir)

        # 修改上传文件名并保存
        ext = os.path.splitext(form2_infos['temp_path'])[1]
        upnew_filename = 'raw-' + form2_infos['MoID'] + '-' + str(form2_infos['LotID']) + '-' + str(lotID2) + ext
        upload_path = os.path.join(uploadsDir, upnew_filename)
        form2_infos['UploadPath'] = upload_path
        shutil.copy(form2_infos['temp_path'], form2_infos['UploadPath'])

        # 获取标准件的测试时间
        get_time = f"SELECT StandardPartNO as TestNO,CONVERT(char,TestTime,120) as StandardTime " \
                   f"FROM StandardPartData WHERE " \
                   f"StandardPartID='{form2_infos['StandardPartID']}' AND  DataStatus=1"
        time_df = pd.read_sql(get_time, engine)
        # 获取的测试时间合并到测试数据中
        test_data = pd.merge(time_df, test_data, on='TestNO')
        # 获取测试数据
        pars = ['MoID', 'StandardPartID', 'MachineID', 'UserID', 'LotID', 'LotID2']
        for i in pars:
            test_data[i] = form2_infos[i]
        try:
            # 数据写入数据库
            test_data.to_sql('Gencoef_Data1', engine, if_exists='append', index=False)
        except Exception as e:
            logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--Gencoef_Data1存储错误,{e}")
            flash_result[100] = f"{machine_label}<br>校准数据Gencoef_Data1存储错误,截图反馈至实验室<br>问题描述:{e}"
        else:
            # 情况6：不存在与校准系数行
            if '校准系数' not in list(coef_data.index):
                logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单不存在'校准系数'行")
                flash_result[6] = "校准系数获取失败！"
                return flash_result, coefconf_path, lotID2, upload_path
            else:
                dict_coef_data = coef_data.loc['校准系数'].to_dict()
                # 情况7：色坐标差异过大
                if (abs(dict_coef_data['dx']) > 0.1 or abs(dict_coef_data['dy']) > 0.1):
                    logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--机差超过1%")
                    flash_result[7] = f"{machine_label}<br>机台差异超10%,暂停使用,联系实验室进行机台校准"
                else:
                    if CCT:
                        if CCT == 'low':
                            code = '低色温-校准系数'
                        elif CCT == 'high':
                            code = '高色温-校准系数'
                        else:
                            code = '校准系数'
                    else:
                        code = '校准系数'
                    # 查询COF中是否存在之前订单、机台一样的数据
                    sql_select = f"SELECT max(StandardPartNO) FROM COF WHERE MoID='{form2_infos['MoID']}' and CF='{code}' and " \
                                 f"MachineID='{form2_infos['MachineID']}'"
                    row = cursor1.execute(sql_select).fetchall()
                    if row[0][0]:
                        No = int(row[0][0]) + 1
                    else:
                        No = 1
                    sql_insert = f"INSERT INTO COF (MoID,CF,ITEM_CODE,StandardPartNO," \
                                 f"dU,dx,dy,dRa,dR9,dlm,dI,MachineID,Updatetime,LotID) " \
                                 f"values ('{form2_infos['MoID']}','{code}','{item_code}','{No}'," \
                                 f"{dict_coef_data['dU']},{dict_coef_data['dx']},{dict_coef_data['dy']}," \
                                 f"{dict_coef_data['dRa']},{dict_coef_data['dR9']},{dict_coef_data['dlm']}," \
                                 f"{dict_coef_data['dI']},'{form2_infos['MachineID']}','{datetime.now()}'," \
                                 f"'{str(form2_infos['LotID']) + '-' + str(form2_infos['LotID2'])}')"
                    try:
                        cursor1.execute(sql_insert)
                        conn.commit()
                    except pyodbc.OperationalError:
                        flash_result[100] = f'数据库连接断开!请关闭网页后,重新进行校准'
                        logger.error(f"数据库连接断开!")
                    except pyodbc.InterfaceError:
                        flash_result[100] = f'数据库登陆失败!请关闭网页后,重新进行校准'
                        logger.error(f"数据库登陆失败!")
                    except Exception as e:
                        flash_result[404] = f'{machine_label}<br>' \
                                            f'第二步校准系数存储时发生错误,截图反馈至实验室<br>问题描述:{e}'
                        logger.error(f"数据库COF表插入失败，问题描述:{e}")
                    else:
                        # 读取下发的配置文件
                        ini_file_read(form2_infos['InitPath'])

                        # 连片
                        if form2_infos['MachineID'] == 3:
                            set_cal_factor_3(dict_coef_data)
                        # 单颗需要同时设置系数范围及对应的bin号
                        elif form2_infos['MachineID'] == 4:
                            set_cal_factor_4(dict_coef_data, get_spec_range(item_code), get_bin_range(item_code))
                        # 一体
                        elif form2_infos['MachineID'] == 5 or form2_infos['MachineID'] == 22:
                            set_cal_factor_5(dict_coef_data, get_spec_range(item_code), get_bin_range(item_code))

                        # 将修改后的配置文件另存为
                        if CCT:
                            str1 = '-低色温' if CCT == 'low' else '-高色温'
                            down_filename = '生成-' + form2_infos['MoID'] + '-' + str(
                                form2_infos['LotID']) + '-' + str(
                                lotID2) + str1 + '.ini'
                        else:
                            down_filename = '生成-' + form2_infos['MoID'] + '-' + str(
                                form2_infos['LotID']) + '-' + str(
                                lotID2) + '.ini'
                        coefconf_path = os.path.join(downloadsDir, down_filename)
                        form2_infos['DownloadPath'] = coefconf_path
                        ini_file_save(coefconf_path)

                        # 列表插入当前时间
                        form2_infos['UpdateTime'] = str(datetime.now())
                        # 删除字典temp_path及临时文件
                        os.remove(form2_infos.pop('temp_path'))
                        # 遍历字典
                        kvs = [(k, v) for k, v in form2_infos.items() if v is not None]
                        # 创建sql语句
                        sql_data = f"INSERT INTO Gencoef_Info1({','.join(i[0] for i in kvs)}) " \
                                   f"VALUES ({','.join(repr(i[1]) for i in kvs)});"
                        try:
                            cursor.execute(sql_data)
                            cnxn.commit()
                        except pyodbc.OperationalError:
                            flash_result[100] = f'数据库连接断开!请关闭网页后,重新进行校准'
                            logger.error(f"数据库连接断开!")
                        except pyodbc.InterfaceError:
                            flash_result[100] = f'数据库登陆失败!请关闭网页后,重新进行校准'
                            logger.error(f"数据库登陆失败!")
                        except Exception as e:
                            flash_result[404] = f'{machine_label}<br>' \
                                                f'第二步校准信息存储时发生错误,截图反馈至实验室<br>问题描述:{e}'
                            logger.error(f"数据库Gencoef_Info1表插入失败，问题描述:{e}")
                        else:
                            flash_result['成功'] = '中间系数计算成功'
                            logger.info(
                                f"{form2_infos['MachineID']}号，{form2_infos['MoID']}工单--中间系数计算成功-{form2_infos['InitPath']}")
    return flash_result, coefconf_path, lotID2, upload_path
